import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.styles import Alignment, PatternFill, colors
# 居中
align = Alignment(horizontal='center',vertical='center',wrap_text=True)

def ReadSheet(wb, sheetName = 'risk_summary', backUpData = None):
    try:
        sht = wb[sheetName]
    except Exception as err:
        print('open {0} sheet failed!'.format(sheetName))
        if backUpData != None:
            return ReadSheet(wb, backUpData)
        else:
            return None
    # print(sht.max_row)
    # print(sht.max_column)
    dataList = []
    for row in sht.rows:
        rowData = []
        if row[0].value == None:
            continue
        for cell in row:
            rowData.append(cell.value)
        dataList.append(rowData)
    del dataList[0] # del title
    # print(dataList[0])
    # print(dataList[1])
    # print(dataList[-1])
    return dataList

def RemoveDupLib4FullResult(frList):
    frDict = {}
    for fr in frList:
        if fr[0] not in frDict:
            frDict[fr[0]] = []
        frDict[fr[0]].append(fr[1:])
    # print(len(frDict))
    return frDict

def RemoveDupSym(frDict):
    symDict = {}
    for libName, descriptions in frDict.items():
        tmpDict = {}
        for des in descriptions:
            if des[0] not in tmpDict:
                tmpDict[des[0]] = {'descriptions' : des[1 : 7], 'locations' : []}
            tmpDict[des[0]]['locations'].append(des[7] + " +" + str(des[8]))
        symDict[libName] = tmpDict
    # print(len(symDict))
    return symDict

def WriteRiskSummary2xlsx(wb, symDict, sheetName = 'risk_summary'):
    sheets = wb.sheetnames
    if sheetName in sheets:
        wb.remove(wb[sheetName])
    rssheet = wb.create_sheet(sheetName, 0)
    #表头
    rssheet.cell(row = 1, column = 1, value = '三方库名')
    rssheet.cell(row = 1, column = 2, value = '符号名')
    rssheet.cell(row = 1, column = 3, value = '类型')
    rssheet.cell(row = 1, column = 4, value = '头文件')
    rssheet.cell(row = 1, column = 5, value = '所属模块')
    rssheet.cell(row = 1, column = 6, value = '状态')
    rssheet.cell(row = 1, column = 7, value = 'OH替换')
    rssheet.cell(row = 1, column = 8, value = '备注')
    rssheet.cell(row = 1, column = 9, value = '出现位置')
    rssheet.cell(row = 1, column = 10, value = '出现数量')
    i = 2
    dims = {1 : 8, 2 : 6, 3 : 4, 4 : 6, 5 : 8, 6 : 4, 7 : 6, 8 : 10, 9 : 8, 10 : 8}
    thirdLibList = []
    totalColumn = 10
    totalRow = 0
    for libName, val in symDict.items():
        thirdLibList.append(libName)
        for sym, data in val.items():
            totalRow += 1
            rssheet.cell(row = i, column = 1, value = libName)
            dims[1] = len(libName) if dims[1] < len(libName) else dims[1]
            rssheet.cell(row = i, column = 2, value = sym)
            dims[2] = len(sym) if dims[2] < len(sym) else dims[2]
            rssheet.cell(row = i, column = 3, value = data['descriptions'][0])
            if data['descriptions'][0] != None:
                dims[3] = len(data['descriptions'][0]) if dims[3] < len(data['descriptions'][0]) else dims[3]
            rssheet.cell(row = i, column = 4, value = data['descriptions'][1])
            if data['descriptions'][1] != None:
                dims[4] = len(data['descriptions'][1]) if dims[4] < len(data['descriptions'][1]) else dims[4]
            rssheet.cell(row = i, column = 5, value = data['descriptions'][2])
            if data['descriptions'][2] != None:
                dims[5] = len(data['descriptions'][2]) if dims[5] < len(data['descriptions'][2]) else dims[5]
            rssheet.cell(row = i, column = 6, value = data['descriptions'][3])
            if data['descriptions'][3] != None:
                dims[6] = len(data['descriptions'][3]) if dims[6] < len(data['descriptions'][3]) else dims[6]
            rssheet.cell(row = i, column = 7, value = data['descriptions'][4])
            if data['descriptions'][4] != None:
                dims[7] = len(data['descriptions'][4]) if dims[7] < len(data['descriptions'][4]) else dims[7]
            rssheet.cell(row = i, column = 8, value = data['descriptions'][5])
            rssheet.cell(row = i, column = 9, value = str(data['locations']).replace("'", '"'))
            rssheet.cell(row = i, column = 10, value = len(data['locations']))
            i += 1
    # 设置列宽
    for colum, width in dims.items():
        rssheet.column_dimensions[get_column_letter(colum)].width = width + 1
    # 设置筛选
    rssheet.auto_filter.ref = "A1:" + get_column_letter(totalColumn) + str(totalRow)
    return len(symDict)

def RemoveDupStatus(rsList):
    libDict = {}
    for rs in rsList:
        if rs[0] not in libDict:
            libDict[rs[0]] = []
                              #api    #状态  #个数
        libDict[rs[0]].append([rs[1], rs[5], rs[9]])
    # print(len(libDict))
    return libDict

def CountStatus(libDict):
    statusDict = {}
    for libName, apiStatus in libDict.items():
        # print(libName, '  ', len(apiStatus))
        tmpDict = {}
        for stat in apiStatus:
            if stat[1] not in tmpDict:
                tmpDict[stat[1]] = 0
            tmpDict[stat[1]] += 1
        statusDict[libName] = tmpDict
    # print(len(statusDict))
    for k,v in statusDict.items():
        print(k , " ", v)
    return statusDict

def WriteLibConclusion2Sheet(wb, statusDict, sheetName = 'lib_Conclusion'):
    sheets = wb.sheetnames
    if sheetName in sheets:
        wb.remove(wb[sheetName])
    csheet = wb.create_sheet(sheetName, 0)
    #表头
    csheet.cell(row = 1, column = 1, value = '三方库名')
    csheet.cell(row = 1, column = 2, value = '风险分析')
    csheet.cell(row = 1, column = 3, value = '风险项')
    csheet.cell(row = 1, column = 4, value = '风险项个数')
    csheet.cell(row = 1, column = 5, value = '三方库团队建议')
    csheet.cell(row = 1, column = 6, value = '预估工作量')
    i = 2
    dims = {1 : 6, 2 : 8, 3 : 6, 4 : 10, 5 : 14, 6 : 10}
    mergeCount = []
    totalColumn = 6
    totalRow = 0
    riskLibs = []
    for libName, val in statusDict.items():
        mergeCount.append(len(val))
        riskLibs.append(libName)
        for stat, count in val.items():
            totalRow += 1
            csheet.cell(row = i, column = 1, value = libName)
            dims[1] = len(libName) if dims[1] < len(libName) else dims[1]
            csheet.cell(row = i, column = 3, value = stat)
            dims[3] = len(stat) if dims[3] < len(stat) else dims[3]
            csheet.cell(row = i, column = 4, value = count)
            i += 1
    for colum, width in dims.items():
        csheet.column_dimensions[get_column_letter(colum)].width = width + 1
    # 合并单元格
    mergeStart = 2
    for i in mergeCount:
        # print(i)
        # print("mergeStart ", mergeStart)
        if i == 1:
            mergeStart += i
            continue
        # print("mergeStart ", mergeStart)
        csheet.merge_cells("A" + str(mergeStart) + ":A" + str(mergeStart + i - 1))
        csheet["A" + str(mergeStart)].alignment = align
        mergeStart = mergeStart + i
    # 设置筛选
    csheet.auto_filter.ref = "A1:" + get_column_letter(totalColumn) + str(totalRow)
    return riskLibs

def WriteOkLibInfo(wb, okLibs, riskLibs, buildScript, sheetName = 'lib_Conclusion'):
    sheets = wb.sheetnames
    if sheetName in sheets:
        csheet = wb[sheetName]
    #表头
    csheet.cell(row = 1, column = 8, value = '三方库名')
    csheet.cell(row = 1, column = 9, value = '构建方式')
    csheet.cell(row = 1, column = 10, value = 'e2e是否支持')
    i = 2
    dims = {8 : 8, 9 : 20, 10 : 11}
    okDict = {"Supted" : [], "NotSupt" : []}
    for libName, bsInfo in buildScript.items():
        if libName not in okLibs:
            continue
        csheet.cell(row = i, column = 8, value = libName)
        dims[8] = len(libName) if dims[8] < len(libName) else dims[8]
        csheet.cell(row = i, column = 9, value = str(bsInfo))
        e2eSupport = '否'
        if "cmake" in bsInfo:
            e2eSupport = '是'
            okDict["Supted"].append(libName)
        else:
            okDict["NotSupt"].append(libName)
        csheet.cell(row = i, column = 10, value = e2eSupport)
        i += 1
    for colum, width in dims.items():
        csheet.column_dimensions[get_column_letter(colum)].width = width + 1
    if len(okLibs) != 0:
        print('风险扫描通过 {0} 库, e2e构建融入工具支持 {1} 个, 占比 {2}%'.format(len(okLibs), len(okDict["Supted"]), round(len(okDict["Supted"]) * 100/len(okLibs), 3)))

    riskBuildDict = {"Supted" : [], "NotSupt" : []}
    print("buildScript ", buildScript)
    for libName, bsInfo in buildScript.items():
        if libName not in riskLibs:
            continue
        if "cmake" in bsInfo:
            riskBuildDict["Supted"].append(libName)
        else:
            riskBuildDict["NotSupt"].append(libName)
    return okDict, riskBuildDict

def WriteProjectConclusion2Sheet(wb, projSymInfo, projectName, sheetName = "Project_Conclusion"):
    # projInfo = []
    apiInfo = {}
    for libName, val in projSymInfo.items():
        for sym, data in val.items():
            if sym not in apiInfo:
                apiInfo[sym] = [data['descriptions'][3], 0, data['descriptions'][0]]
            apiInfo[sym][1] += len(data['locations'])
        # projInfo.append(apiInfo)
    # print(apiInfo)
    sheets = wb.sheetnames
    if sheetName in sheets:
        wb.remove(wb[sheetName])
    pcsheet = wb.create_sheet(sheetName, 0)
    #表头
    pcsheet.cell(row = 1, column = 1, value = '项目')
    pcsheet.cell(row = 1, column = 2, value = '符号')
    pcsheet.cell(row = 1, column = 3, value = '类型')
    pcsheet.cell(row = 1, column = 4, value = '状态')
    pcsheet.cell(row = 1, column = 5, value = '出现次数')
    pcsheet.cell(row = 1, column = 6, value = '预估工作量')
    pcsheet.cell(row = 1, column = 7, value = '备注')
    i = 2
    dims = {1 : 4, 2 : 4, 3 : 4, 4 : 4, 5 : 8, 6 : 10, 7 : 4}
    mergeStart = 2
    ratioStatistics = {}
    if apiInfo:
        for k, v in apiInfo.items():
            pcsheet.cell(row = i, column = 1, value = projectName)
            dims[1] = len(projectName) if dims[1] < len(projectName) else dims[1]
            pcsheet.cell(row = i, column = 2, value = k)
            dims[2] = len(k) if dims[2] < len(k) else dims[2]
            pcsheet.cell(row = i, column = 3, value = v[2])
            dims[3] = len(v[2]) if dims[3] < len(v[2]) else dims[3]
            pcsheet.cell(row = i, column = 4, value = v[0])
            if v[0] not in ratioStatistics:
                ratioStatistics[v[0]] = 0
            ratioStatistics[v[0]] += 1
            dims[4] = len(v[0]) if dims[4] < len(v[0]) else dims[4]
            pcsheet.cell(row = i, column = 5, value = v[1])
            i += 1
        pcsheet.merge_cells("A" + str(mergeStart) + ":A" + str(i - 1))
        pcsheet["A" + str(mergeStart)].alignment = align
        for colum, width in dims.items():
            pcsheet.column_dimensions[get_column_letter(colum)].width = width + 1
        j = 2
        for 状态, count in ratioStatistics.items():
            pcsheet.cell(row = j, column = 9, value = 状态)
            pcsheet.cell(row = j, column = 10, value = "{}%".format(round(count*100/(i-2), 3)))
            j += 1
        pcsheet.column_dimensions[get_column_letter(9)].width = dims[4] + 1
    print('造成库未通过的 API 共 {0} 个'.format(i - 2))

def DataAnalys(wb, projectName, buildScript=None):

    rsList = ReadSheet(wb, 'origin_data') # origin_data
    frlibDict = RemoveDupLib4FullResult(rsList)
    symDict = RemoveDupSym(frlibDict)
    WriteRiskSummary2xlsx(wb, symDict, 'risk_summary')

    rsList = ReadSheet(wb, 'risk_summary')
    libDict = RemoveDupStatus(rsList)
    statusDict = CountStatus(libDict)

    riskLibs = WriteLibConclusion2Sheet(wb, statusDict, 'lib_Conclusion')
    WriteProjectConclusion2Sheet(wb, symDict, projectName, "Project_Conclusion")

    return riskLibs

def MergeCell4Overview(overviewSheet):
    # 设置列宽度
    overviewSheet.column_dimensions[get_column_letter(1)].width = 15
    overviewSheet.column_dimensions[get_column_letter(2)].width = 30
    overviewSheet.column_dimensions[get_column_letter(3)].width = 30
    overviewSheet.column_dimensions[get_column_letter(4)].width = 75
    # 合并居中换行
    overviewSheet.merge_cells("A1:A41")
    overviewSheet["A1"].alignment = align
    overviewSheet["A1"].fill = PatternFill("solid", fgColor = "6495ED")
    overviewSheet.merge_cells("B1:B19")
    overviewSheet["B1"].alignment = align
    overviewSheet["B1"].fill = PatternFill("solid", fgColor = "90EE90")
    overviewSheet.merge_cells("B20:B41")
    overviewSheet["B20"].alignment = align
    overviewSheet["B20"].fill = PatternFill("solid", fgColor = "FFE4B5")
    overviewSheet.merge_cells("C1:C11")
    overviewSheet["C1"].alignment = align
    overviewSheet["C1"].fill = PatternFill("solid", fgColor = "008000")
    overviewSheet.merge_cells("C12:C19")
    overviewSheet["C12"].alignment = align
    overviewSheet["C12"].fill = PatternFill("solid", fgColor = "32CD32")
    overviewSheet.merge_cells("D1:D11")
    overviewSheet["D1"].alignment = align
    overviewSheet["D1"].fill = PatternFill("solid", fgColor = "008000")
    overviewSheet.merge_cells("D12:D19")
    overviewSheet["D12"].alignment = align
    overviewSheet["D12"].fill = PatternFill("solid", fgColor = "32CD32")
    overviewSheet.merge_cells("C20:C31")
    overviewSheet["C20"].alignment = align
    overviewSheet["C20"].fill = PatternFill("solid", fgColor = "F4A460")
    overviewSheet.merge_cells("C32:C41")
    overviewSheet["C32"].alignment = align
    overviewSheet["C32"].fill = PatternFill("solid", fgColor = "FFA500")
    overviewSheet.merge_cells("D20:D31")
    overviewSheet["D20"].alignment = align
    overviewSheet["D20"].fill = PatternFill("solid", fgColor = "F4A460")
    overviewSheet.merge_cells("D32:D41")
    overviewSheet["D32"].alignment = align
    overviewSheet["D32"].fill = PatternFill("solid", fgColor = "FFA500")

# 这张表，结构定死
def WriteOverview(wb, projectOverview, sheetName = 'project_overview'):
    sheets = wb.sheetnames
    if sheetName in sheets:
        wb.remove(wb[sheetName])
    overviewSheet = wb.create_sheet(sheetName, 0)
    MergeCell4Overview(overviewSheet)
    # 写入数据
    buildSupportCount = len(projectOverview["NORiskLib"]["Supted"])
    buildNotSupportCount = len(projectOverview["NORiskLib"]["NotSupt"])
    noRiskCount = buildSupportCount + buildNotSupportCount

    RbuildSupportCount = len(projectOverview["RiskLib"]["Supted"])
    RbuildNotSupportCount = len(projectOverview["RiskLib"]["NotSupt"])
    riskCount = RbuildSupportCount + RbuildNotSupportCount

    libsCount = noRiskCount + riskCount
    overviewSheet["A1"] = projectOverview["ProjectName"] + "共 {0} 个库".format(libsCount)
    overviewSheet["B1"] = "风险扫描通过 {0} 个库,占比 {1}%".format(noRiskCount, noRiskCount * 100 / libsCount)
    overviewSheet["B20"] = "风险扫描未通过 {0} 个库,占比 {1}%".format(riskCount, riskCount * 100 / libsCount)

    overviewSheet["C1"] = "构建工具支持 {0} 个库,占总体比例 {1}%".format(buildSupportCount, buildSupportCount * 100 / libsCount)
    overviewSheet["C12"] = "构建工具不支持 {0} 个库,占总体比例 {1}%".format(buildNotSupportCount, buildNotSupportCount * 100 / libsCount)
    overviewSheet["D1"] = str(projectOverview["NORiskLib"]["Supted"])
    overviewSheet["D12"] = str(projectOverview["NORiskLib"]["NotSupt"])

    overviewSheet["C20"] = "构建工具支持 {0} 个库,占总体比例 {1}%".format(RbuildSupportCount, RbuildSupportCount * 100 / libsCount)
    overviewSheet["C32"] = "构建工具不支持 {0} 个库,占总体比例 {1}%".format(RbuildNotSupportCount, RbuildNotSupportCount * 100 / libsCount)
    overviewSheet["D20"] = str(projectOverview["RiskLib"]["Supted"])
    overviewSheet["D32"] = str(projectOverview["RiskLib"]["NotSupt"])

import sys

if __name__ == '__main__':

    if len(sys.argv) != 3:
        print("""使用方法: python data_summary.py xlsx文件路径 项目名""")
    excelFile = sys.argv[1]
    wb = openpyxl.load_workbook(excelFile)

    DataAnalys(wb, sys.argv[2])
    wb.save(excelFile)
    # pass